import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class JdbcInsert1 {
    public static void main(String[] args) throws SQLException {
        //与数据库建立链接
        A.func();
       Select select = new Select();
       select.select();
        A.close();

    }
}

class A {
    Scanner scanner = new Scanner(System.in);
    static PreparedStatement statement;
     static DataSource dataSource;
    static Connection connection;
    public static void func() throws SQLException {
        //JDBC需要通过以下步骤来完成开发
        //1. 创建并初始化一个数据源
        dataSource = new MysqlDataSource();
        //为什么要强转回来?
        //因为setUrl这个方法是子类有的父类没有
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/3_15?characterEncoding=utf8&useSSL=false");//向下转型 父类变子类
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("111111");
        //2. 和数据库服务器连接
        connection = dataSource.getConnection();

    }
    public  static void close() throws SQLException {
        //5. 释放必要的资源
        statement .close();
        System.out.println();
        connection.close();
    }
    public static void run() throws SQLException {
        int ret1 = statement.executeUpdate();//把与编译后的语句发送给服务器
        System.out.println(ret1);//返值是影响到的行数

    }
}

class Add extends A{


    public void insert () throws SQLException {
        System.out.println("请输入学生ID");
        int ID = scanner.nextInt();
        System.out.println("请输入学生姓名");
        String name = scanner.next();
        String sql = "insert into student values(? , ?)";
        statement = connection.prepareStatement(sql);
        statement.setInt(1,ID);
        statement.setString(2,name);
        System.out.println(statement);
        run();
    }


}
class Delete extends A{

    public  void delete() throws SQLException {
        System.out.println("请输入要删除学生的ID");
        int ID = scanner.nextInt();
        String sql = "delete from student where ID = ?";
         statement = connection.prepareStatement(sql);
         statement.setInt(1,ID);
        run();

    }
}
class Update extends A{
    String sql;
    public void update() throws SQLException {
        System.out.println("请输入要修改学生的ID");
        int ID = scanner.nextInt();
        System.out.println("修改姓名请输入->1,修改学号请输入->2,两者都修改请输入->3,退出修改请输入->4");
        while (true) {
            int ret =scanner.nextInt();
            if (ret == 1) {
                System.out.println("请输入新名字");
                String newName = scanner.next();
               sql = "update student  set name = ? where ID = ?";
               statement = connection.prepareStatement(sql);
                statement.setString(1,newName);
               statement.setInt(2,ID);
                break;
            }else if(ret == 2) {
                System.out.println("请输入新ID");
                int newID = scanner.nextInt();
                sql = "update  student set id = ? where ID = ?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1,newID);
                statement.setInt(2,ID);

                break;
            }else if (ret == 3) {
                System.out.println("请输入新名字");
                String newName = scanner.next();
                System.out.println("请输入新ID");
                int newID = scanner.nextInt();
                sql = "update student  set id = ?,name = ? where ID = ?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1,newID);
                statement.setString(2,newName);
                statement.setInt(3,ID);
                break;
          }else if (ret == 4) {
              return;
          }else {
              System.out.println("输入错误,请重新输入");
          }
      }
        run();
    }

}
class Select extends A{

    public void select() throws SQLException {
        String sql = "select * from student";
       statement = connection.prepareStatement(sql);//用这个东西对sql语句先进行预解析
        ResultSet resultSet = statement.executeQuery();
        System.out.println(statement);
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println("id = "+id+" name = "+name);
        }

    }

}

